﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using Yz.Base;
using Yz.Base.Enums;

namespace Yz.Core.Tools
{
    public class YzNPOI
    {
        #region 导出
        /// <summary>
        ///     导出Excel
        /// </summary>
        /// <param name=" npoiExcel">导出的Excel对象</param>
        /// <returns></returns>
        public static MemoryStream ExportExcel(NPOIExcel npoiExcel)
        {
            IWorkbook workbook = null;
            if (npoiExcel.ExcelType == ExcelType.Xls)
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }
            #region 文件属性信息

            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = BaseConfig.SysName;
            //workbook.DocumentSummaryInformation = dsi;
            var si = PropertySetFactory.CreateSummaryInformation();
            si.Author = BaseConfig.SysName;
            si.ApplicationName = BaseConfig.SysName; //填加xls文件创建程序信息
            si.LastAuthor = BaseConfig.SysName; //填加xls文件最后保存者信息
            si.Comments = BaseConfig.SysName; //填加xls文件作者信息
            si.Title = BaseConfig.SysName; //填加xls文件标题信息
            si.Subject = BaseConfig.SysName; //填加文件主题信息
            si.CreateDateTime = DateTime.Now;
            // workbook.SummaryInformation = si;

            #endregion 文件属性信息

            var retn = false;
            if (npoiExcel == null || npoiExcel.NPOISheets.Count == 0)
            {
                retn = true;
            }
            else
            {
                foreach (var sht in npoiExcel.NPOISheets)
                {
                    if (sht.Columns.Count == 0 || sht.Data.Columns.Count < sht.Columns.Count ||
                         sht.TitleRowCount == 0)
                    {
                        retn = true;
                        break;
                    }
                }
            }
            if (retn)
            {
                if (npoiExcel.ExcelType == ExcelType.Xls)
                {
                    using (var ms = new MemoryStream())
                    {
                        workbook.Write(ms);
                        ms.Flush();
                        ms.Position = 0;
                        return ms;
                    }
                }
                else
                {
                    NPOIMemoryStream npoiMs = new NPOIMemoryStream
                    {
                        AllowClose = false
                    };
                    workbook.Write(npoiMs);
                    npoiMs.Flush();
                    npoiMs.Position = 0;
                    workbook = null;
                    return npoiMs;
                }
            }
            ISheet sheet = null;

            #region 超链接样式
            var linkStyle = workbook.CreateCellStyle();
            linkStyle.Alignment = HorizontalAlignment.Center;
            linkStyle.VerticalAlignment = VerticalAlignment.Center;
            var linkfont = workbook.CreateFont();
            linkfont.Underline = FontUnderlineType.Single;
            linkfont.FontHeightInPoints = 10;
            linkfont.IsBold = true;
            linkfont.Color = HSSFColor.Blue.Index;
            linkStyle.SetFont(linkfont);
            linkStyle.WrapText = false;

            var linkStyleCell = workbook.CreateCellStyle();
            linkStyleCell.BorderTop = BorderStyle.Thin;
            linkStyleCell.BorderRight = BorderStyle.Thin;
            linkStyleCell.BorderLeft = BorderStyle.Thin;
            linkStyleCell.BorderBottom = BorderStyle.Thin;
            linkStyleCell.WrapText = false;
            linkStyleCell.Alignment = HorizontalAlignment.Center;
            linkStyleCell.FillForegroundColor = HSSFColor.White.Index;
            linkStyleCell.FillPattern = FillPattern.FineDots;
            linkStyleCell.FillBackgroundColor = HSSFColor.White.Index;
            linkStyleCell.VerticalAlignment = VerticalAlignment.Center;
            var linkfontCell = workbook.CreateFont();
            linkfontCell.Underline = FontUnderlineType.Single;
            linkfontCell.FontHeightInPoints = 10;
            linkfontCell.IsBold = true;
            linkfontCell.Color = HSSFColor.Blue.Index;
            linkStyleCell.SetFont(linkfontCell);
            #endregion

            #region 表格数据样式（单身）

            var dataStyle = workbook.CreateCellStyle();
            dataStyle.BorderTop = BorderStyle.Thin;
            dataStyle.BorderRight = BorderStyle.Thin;
            dataStyle.BorderLeft = BorderStyle.Thin;
            dataStyle.BorderBottom = BorderStyle.Thin;
            dataStyle.WrapText = false;
            dataStyle.FillForegroundColor = HSSFColor.White.Index;
            dataStyle.FillPattern = FillPattern.FineDots;
            dataStyle.FillBackgroundColor = HSSFColor.White.Index;
            dataStyle.VerticalAlignment = VerticalAlignment.Center;

            #endregion 表格数据样式（单身）

            #region 标题样式

            var titleStyle = workbook.CreateCellStyle();
            titleStyle.Alignment = HorizontalAlignment.Center;
            titleStyle.WrapText = false;
            titleStyle.BorderLeft = BorderStyle.Thin;
            titleStyle.BorderRight = BorderStyle.Thin;
            titleStyle.BorderTop = BorderStyle.Thin;
            var titlefont = workbook.CreateFont();
            titlefont.FontHeightInPoints = 15;
            titlefont.IsBold = true;
            titleStyle.SetFont(titlefont);

            #endregion 标题样式

            #region 列头样式

            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            var headfont = workbook.CreateFont();
            headfont.FontHeightInPoints = 10;
            headfont.IsBold = true;
            headStyle.SetFont(headfont);
            headStyle.WrapText = false;

            #endregion 列头样式

            for (var ti = 0; ti < npoiExcel.NPOISheets.Count; ti++)
            {
                var dt = npoiExcel.NPOISheets[ti].Data;
                sheet = workbook.CreateSheet(npoiExcel.NPOISheets[ti].SheetName);
                var rowIndex = npoiExcel.NPOISheets[ti].EmptyRowCount;
                #region 表头列头

                #region 大标题

                if (!string.IsNullOrEmpty(npoiExcel.NPOISheets[ti].TitleName))
                {
                    var titleRow = sheet.CreateRow(rowIndex);
                    titleRow.HeightInPoints = 25;
                    for (var j = 0; j < npoiExcel.NPOISheets[ti].Columns.Count; j++) //循环列
                    {
                        int cellIndex = j + npoiExcel.NPOISheets[ti].EmptyColCount;
                        titleRow.CreateCell(cellIndex).SetCellValue(npoiExcel.NPOISheets[ti].TitleName);
                        titleRow.GetCell(cellIndex).CellStyle = titleStyle;
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, npoiExcel.NPOISheets[ti].EmptyColCount, npoiExcel.NPOISheets[ti].Columns.Count - 1 + npoiExcel.NPOISheets[ti].EmptyColCount));
                    rowIndex++;
                }

                #endregion 大标题

                #region 列头

                for (var i = 0; i < npoiExcel.NPOISheets[ti].TitleRowCount; i++) //循环行
                {
                    var headerRow = sheet.CreateRow(rowIndex);
                    for (var j = 0; j < npoiExcel.NPOISheets[ti].Columns.Count; j++) //循环列
                    {
                        int cellIndex = j + npoiExcel.NPOISheets[ti].EmptyColCount;
                        headerRow.CreateCell(cellIndex).SetCellValue(npoiExcel.NPOISheets[ti].Columns[j].ColTitles[i]); //赋值表头
                        headerRow.GetCell(cellIndex).CellStyle = headStyle;
                        if (i == 0)
                        {
                            sheet.SetColumnWidth(cellIndex, npoiExcel.NPOISheets[ti].Columns[j].ColWidth);
                        }
                        string colComment = npoiExcel.NPOISheets[ti].Columns[j].ColComment;
                        if (!string.IsNullOrEmpty(colComment))
                        {
                            IClientAnchor clientAnchor = null;
                            IRichTextString richeTextString = null;
                            if (npoiExcel.ExcelType == ExcelType.Xls)
                            {
                                clientAnchor = new HSSFClientAnchor();
                                richeTextString = new HSSFRichTextString(colComment);
                            }
                            else
                            {
                                clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, cellIndex, i, cellIndex + 1, i + 1);
                                richeTextString = new XSSFRichTextString(colComment);
                            }
                            var cellCmt = sheet.CreateDrawingPatriarch().CreateCellComment(clientAnchor);
                            cellCmt.String = richeTextString;
                            headerRow.GetCell(cellIndex).CellComment = cellCmt;
                        }
                    }
                    rowIndex++;
                }

                #endregion 列头

                #endregion 表头列头

                foreach (DataRow dr in dt.Rows)
                {
                    var headerRow = sheet.CreateRow(rowIndex);
                    for (var i = 0; i < npoiExcel.NPOISheets[ti].Columns.Count; i++)
                    {
                        int cellIndex = i + npoiExcel.NPOISheets[ti].EmptyColCount;
                        headerRow.CreateCell(cellIndex).SetCellValue(dr[npoiExcel.NPOISheets[ti].Columns[i].ColName].ToString());
                        dataStyle.Alignment = (HorizontalAlignment)Convert.ToInt32(npoiExcel.NPOISheets[ti].Columns[i].ColAlign);
                        if (npoiExcel.NPOISheets[ti].Hyperlinks.Where(p => p.Col == cellIndex && p.Row == rowIndex).Any())
                        {
                            headerRow.GetCell(cellIndex).CellStyle = linkStyleCell;
                        }
                        else
                        {
                            headerRow.GetCell(cellIndex).CellStyle = dataStyle;
                        }
                    }

                    rowIndex++;
                }
                ///合并单元格
                foreach (var item in npoiExcel.NPOISheets[ti].EmrList)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(item.StartRow, item.EndRow, item.StartCol, item.EndCol));
                }
                ///增加超链接
                foreach (var item in npoiExcel.NPOISheets[ti].Hyperlinks)
                {
                    IHyperlink link = null;
                    if (npoiExcel.ExcelType == ExcelType.Xls)
                    {
                        link = new HSSFHyperlink(HyperlinkType.Document)
                        {
                            Address = "#" + item.Address + "!A1"
                        };
                    }
                    else
                    {
                        link = new XSSFHyperlink(HyperlinkType.Document)
                        {
                            Address = "#" + item.Address + "!A1"
                        };
                    }
                    var Row = sheet.GetRow(item.Row);
                    if (Row == null)
                    {
                        Row = sheet.CreateRow(item.Row);
                    }
                    var cell = Row.GetCell(item.Col);
                    if (cell == null)
                    {
                        cell = Row.CreateCell(item.Col);
                        cell.SetCellValue(item.Content);
                        cell.CellStyle = linkStyle;
                    }
                    cell.Hyperlink = link;

                }
            }
            if (npoiExcel.ExcelType == ExcelType.Xls)
            {
                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    sheet = null;
                    workbook = null;
                    return ms;
                }
            }
            else
            {
                NPOIMemoryStream npoiMs = new NPOIMemoryStream
                {
                    AllowClose = false
                };
                workbook.Write(npoiMs);
                npoiMs.Flush();
                npoiMs.Position = 0;
                sheet = null;
                workbook = null;
                return npoiMs;
            }
        }

        #endregion 导出

        #region  导入
        /// <summary>
        /// Excel导入
        /// </summary>
        /// <param name="npoiExcel">导入的Excel对象</param>
        /// <returns></returns>
        public static DataSet ImportExcel(ImportNPOIExcel npoiExcel)
        {
            try
            {
                DataSet ds = new DataSet();
                IWorkbook wb = null;
                if (npoiExcel.ExcelType == ExcelType.Xls)
                {
                    wb = new HSSFWorkbook(npoiExcel.FileStream);
                }
                else
                {
                    wb = new XSSFWorkbook(npoiExcel.FileStream);
                }
                int sheetcount = wb.NumberOfSheets;
                if (sheetcount <= 0)
                {
                    return null;
                }
                for (int x = 0; x < sheetcount; x++)
                {
                    DataTable dt = new DataTable();
                    ISheet sheet = wb.GetSheetAt(x);

                    dt.TableName = sheet.SheetName;
                    int cellCount = 0;
                    bool NoData = false;
                    for (int i = 0; i < npoiExcel.HeaderRowCount; i++)
                    {
                        IRow headerRow = sheet.GetRow(i);
                        if (headerRow == null)
                        {
                            NoData = true;
                            continue;
                        }
                        cellCount = headerRow.LastCellNum;

                        for (int j = 0; j < cellCount; j++)
                        {
                            ICell cell = headerRow.GetCell(j);
                            string tabelTitle = cell.ToString();
                            if (!string.IsNullOrWhiteSpace(tabelTitle))
                            {
                                if (npoiExcel.DicCols != null)
                                {
                                    string colTitle = npoiExcel.DicCols.Where(p => tabelTitle.Contains(p.Key)).Select(p => p.Value).FirstOrDefault();
                                    if (!string.IsNullOrEmpty(colTitle))
                                    {
                                        tabelTitle = colTitle;
                                    }
                                }
                                dt.Columns.Add(tabelTitle);
                            }
                        }
                    }
                    if (NoData)
                    {
                        continue;
                    }

                    for (int i = npoiExcel.HeaderRowCount; i <= sheet.LastRowNum; i++)
                    {
                        IRow Row = sheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();

                        for (int j = Row.FirstCellNum; j < cellCount; j++)
                        {
                            ICell cell = Row.GetCell(j);
                            if (cell == null)
                            {
                                dataRow[j] = null;
                            }
                            else
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        dataRow[j] = null;
                                        break;
                                    case CellType.Boolean:
                                        dataRow[j] = cell.BooleanCellValue;
                                        break;
                                    case CellType.Numeric:
                                        dataRow[j] = Convert.ToDouble(cell.NumericCellValue);
                                        break;
                                    case CellType.String:
                                        dataRow[j] = cell.StringCellValue;
                                        break;
                                    case CellType.Error:
                                        dataRow[j] = cell.ErrorCellValue;
                                        break;
                                    case CellType.Formula:
                                        dataRow[j] = Math.Round(cell.NumericCellValue, 2);
                                        break;
                                    default:
                                        dataRow[j] = "=" + cell.CellFormula;
                                        break;
                                }
                            }
                        }
                        dt.Rows.Add(dataRow);
                    }
                    ds.Tables.Add(dt);
                }
                return ds;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "NPOI导入");
                return null;
            }
        }
        #endregion
    }

    #region 调用类
    public class NPOIMemoryStream : MemoryStream
    {
        public NPOIMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }
    /// <summary>
    ///     NPOI合并单元格索引
    /// </summary>
    public class ExcelMergeRegion
    {
        public int StartRow { get; set; }
        public int EndRow { get; set; }
        public int StartCol { get; set; }
        public int EndCol { get; set; }
    }
    /// <summary>
    /// 导出Excel
    /// </summary>
    public class NPOIExcel
    {    /// <summary>
         /// Excel版本
         /// </summary>
        public ExcelType ExcelType { get; set; } = ExcelType.Xls;
        /// <summary>
        /// sheet集合
        /// </summary>
        public List<NPOISheet> NPOISheets { get; set; }
        /// <summary>
        /// Excel文件名称
        /// </summary>
        public string ExcelName { get; set; }
    }

    /// <summary>
    ///     NPOIsheet
    /// </summary>
    public class NPOISheet
    {
        /// <summary>
        /// sheet名称
        /// </summary>
        public string SheetName { get; set; }
        /// <summary>
        /// 表头名称
        /// </summary>
        public string TitleName { get; set; }
        /// <summary>
        /// 空行数量
        /// </summary>
        public int EmptyRowCount { get; set; } = 0;
        /// <summary>
        /// 空列数量
        /// </summary>
        public int EmptyColCount { get; set; } = 0;
        /// <summary>
        ///     表头数量
        /// </summary>
        public int TitleRowCount { get; set; } = 1;
        /// <summary>
        /// 数据源
        /// </summary>
        public DataTable Data { get; set; }
        /// <summary>
        /// 合并单元格集合
        /// </summary>
        public List<ExcelMergeRegion> EmrList { get; set; } = new List<ExcelMergeRegion>();
        /// <summary>
        /// 列信息集合
        /// </summary>
        public List<NPOIColumn> Columns { get; set; } = new List<NPOIColumn>();
        /// <summary>
        /// 单元格链接（链接到sheet）
        /// </summary>
        public List<NPOIHyperlink> Hyperlinks { get; set; } = new List<NPOIHyperlink>();
    }

    /// <summary>
    ///     单元格
    /// </summary>
    public class NPOIColumn
    {
        /// <summary>
        ///     datatable中对应的字段名称
        /// </summary>
        public string ColName { get; set; }

        /// <summary>
        ///     列宽度
        /// </summary>
        public int ColWidth { get; set; } = 5000;
        /// <summary>
        /// 列头批注
        /// </summary>
        public string ColComment { get; set; } = "";

        /// <summary>
        ///     数据位置
        /// </summary>
        public NPOIColAlign ColAlign { get; set; } = NPOIColAlign.Center;

        /// <summary>
        ///     多级表头
        /// </summary>
        public List<string> ColTitles { get; set; } = new List<string>();
    }

    public enum NPOIColAlign
    {
        General = 0,

        //
        Left = 1,

        //
        Center = 2,

        //
        Right = 3,

        //
        Fill = 4,

        //
        Justify = 5,

        //
        CenterSelection = 6,

        //
        Distributed = 7
    }

    public class NPOIHyperlink
    {
        /// <summary>
        /// sheet名称
        /// </summary>
        public string Address { get; set; }
        /// <summary>
        /// 行
        /// </summary>
        public int Row { get; set; }
        /// <summary>
        /// 列
        /// </summary>
        public int Col { get; set; }
        /// <summary>
        /// 内容
        /// </summary>
        public string Content { get; set; }
    }
    /// <summary>
    /// 导入的Excel类
    /// </summary>
    public class ImportNPOIExcel
    {
        /// <summary>
        /// Excel文件流
        /// </summary>
        public Stream FileStream { get; set; }
        /// <summary>
        /// 头部行数
        /// </summary>
        public int HeaderRowCount { get; set; } = 1;

        /// <summary>
        /// Excel类型
        /// </summary>
        public ExcelType ExcelType { get; set; } = ExcelType.Xls;
        /// <summary>
        /// 头部转换
        /// </summary>
        public Dictionary<string, string> DicCols { get; set; } = new Dictionary<string, string>();
    }
    #endregion 调用类
}
